package com.token.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.google.common.base.CaseFormat;
import com.google.common.collect.Lists;
import com.token.mapper.TestSqlConditionInfoMapper;
import com.token.mapper.TestSqlInfoMapper;
import com.token.model.dto.SqlConditionDTO;
import com.token.model.dto.TestSqlInfoDTO;
import com.token.model.po.TestSqlConditionInfo;
import com.token.model.po.TestSqlInfo;
import com.token.service.TestSqlInfoService;
import com.token.utils.PatternUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * @auther: henriy
 * @Date: 2023/10/30
 */
@Slf4j
@Service
public class TestSqlInfoServiceImpl implements TestSqlInfoService {

    @Resource
    private TestSqlInfoMapper testSqlInfoMapper;

    @Resource
    private TestSqlConditionInfoMapper testSqlConditionInfoMapper;

    @Override
    public void export(TestSqlInfoDTO testSqlInfoDTO, HttpServletResponse response) throws Exception {
        // 参数：表头、字段、条件
        List<String> headList = testSqlInfoDTO.getHeadList();
        List<String> columnList = testSqlInfoDTO.getColumnList();
        List<SqlConditionDTO> conditionList = testSqlInfoDTO.getConditionList();

        List<List<String>> headResultList = Lists.newArrayList();
        List<List<Object>> dataResultList = Lists.newArrayList();
        List<String> columnQueryList = Lists.newArrayList();
        List<String> columnResultList = Lists.newArrayList();
        List<String> columnTempList = Lists.newArrayList();
        String sqlResult;

        String module = testSqlInfoDTO.getModule();
        TestSqlInfo testSqlInfo = testSqlInfoMapper.selectSqlColumnByModule(module);
        if (Objects.isNull(testSqlInfo) || StringUtils.isBlank(testSqlInfo.getSqlContent())) {
            throw new Exception("该模块未配置字段内容");
        }
        log.info("配置sql内容为：{}", testSqlInfo.getSqlContent());

        // 获取Excel中的表头信息
        for (String excelHead : headList) {
            List<String> list = Lists.newArrayList();
            list.add(excelHead);
            headResultList.add(list);
        }
        log.info("表头：{}", headResultList);

        // 是否全部是下划线字段（传参类型一般为下划线字段或驼峰命名字段）
        boolean caseFlag = columnList.stream()
                .allMatch(column -> StringUtils.contains(column, "_"));

        if (caseFlag) {
            log.info("下划线转为驼峰====");

            for (String column : columnList) {
                // 下划线转为驼峰
                String resultStr = CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, column);
                String formatColumn = String.format("%s as %s", column, resultStr);
                columnResultList.add(resultStr);
                columnQueryList.add(formatColumn);
            }
        } else {
            log.info("驼峰转为下划线++++");
            columnResultList.addAll(columnList);

            for (String column : columnList) {
                // 驼峰转为下划线
                String resultStr = CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, column);
                columnQueryList.add(String.format("%s as %s", resultStr, column));
            }
        }

        if (CollectionUtils.isEmpty(conditionList)) {
            // 条件为空
            sqlResult = String.format(testSqlInfo.getSqlContent(), columnQueryList.toArray());
            log.info("无查询条件SQL结果为：{}", sqlResult);
        } else {
            // 条件不为空
            LambdaQueryWrapper<TestSqlConditionInfo> conditionQueryWrapper = Wrappers.lambdaQuery();
            conditionQueryWrapper.eq(TestSqlConditionInfo::getTestNo, testSqlInfo.getTestNo());
            List<TestSqlConditionInfo> conditionFromTableList = testSqlConditionInfoMapper.selectList(conditionQueryWrapper);

            // 1，判断是否配置有条件内容；
            if (CollectionUtils.isEmpty(conditionFromTableList)) {
                throw new Exception("未配置条件内容");
            }

            for (SqlConditionDTO conditionParam : conditionList) {
                String columnName = conditionParam.getColumnName();
                String columnValue = conditionParam.getColumnValue();
                String columnNameResult = CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, columnName);

                List<TestSqlConditionInfo> conditionInfoList = conditionFromTableList.stream()
                        .filter(param -> StringUtils.equals(param.getColumns(), columnNameResult))
                        .collect(Collectors.toList());

                if (CollectionUtils.isEmpty(conditionInfoList)) {
                    log.info("字段：{}暂未配置查询条件", columnNameResult);
                    continue;
                }

                if (columnValue.contains(",")) {
                    String[] splitTime = columnValue.split(",");
                    String beginTime = splitTime[0];
                    String endTime = splitTime[1];

                    // 时间字段
                    List<String> contentList = conditionInfoList.stream()
                            .map(TestSqlConditionInfo::getConditionContent)
                            .collect(Collectors.toList());
                    contentList.forEach(content -> {
                        if (content.contains(">=")) {
                            columnTempList.add(String.format(content, beginTime));
                        } else {
                            columnTempList.add(String.format(content, endTime));
                        }
                    });
                } else {
                    // 非时间字段
                    String conditionContent = conditionInfoList.get(0).getConditionContent();
                    String replaceResult = conditionContent.replace("%s", columnValue);
                    columnTempList.add(replaceResult);
                }
            }

            // 条件拼接结果
            String conditionJoinResult = String.join(" and ", columnTempList);
            log.info("条件拼接结果：{}", conditionJoinResult);

            // 字段结果
            String columnContent = testSqlInfo.getSqlContent();
            String columnContentResult = String.format(columnContent, columnQueryList.toArray());
            log.info("字段结果：{}", columnContentResult);

            // 4，合并为一条完整sql；
            sqlResult = String.format("%s where %s", columnContentResult, conditionJoinResult);
        }

        List<Map<String, Object>> tempList = testSqlInfoMapper.executeSql(sqlResult);
        log.info("SQL查询结果为：{}", tempList);

        if (CollectionUtils.isNotEmpty(tempList)) {
            for (Map<String, Object> map : tempList) {
                System.out.println("map=======" + map);
                List<Object> data = Lists.newArrayList();

                for (String column : columnResultList) {
                    Object columnValue = map.get(column);
                    System.out.println("column===" + column + "，columnValue===" + columnValue);

                    if (Objects.nonNull(columnValue)) {
                        String valueStr = columnValue.toString();
                        System.out.println("valueStr==" + valueStr);
                        data.add(valueStr);
                    }
                }

                dataResultList.add(data);
            }
        }

        // todo：单表：无条件(完成)，有条件的(完成)，时间格式(完成)、涉及图片的可能导不出(导不出)；
        //  多表未开始：无条件(完成)，有条件(完成)，
        //  复杂表头未开始（单表、多表等）
        log.info("导出的数据对象大小为：{}，内容为：{}", dataResultList.size(), dataResultList);
        ExcelWriter excelWriter = null;

        try {
            response.setContentType("application/vnd.openmosix-officiated.spreadsheet.sheet");
            response.setCharacterEncoding("UTF-8");
            String nowTime = DateTimeFormatter.ofPattern("yyyyMMddHHmmss").format(LocalDateTime.now());
            String fileName = URLEncoder.encode(testSqlInfoDTO.getFileName() + nowTime, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            // 新建ExcelWriter
            excelWriter = EasyExcel.write(response.getOutputStream()).build();
            WriteSheet sheet = EasyExcel.writerSheet(0, "sheet")
                    .head(headResultList)
                    .includeColumnFiledNames(columnResultList)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .build();
            excelWriter.write(dataResultList, sheet);

            log.info("{}模块，Excel导出成功>>>>>", module);
        } catch (IOException e) {
            log.error("导出异常：{}", e.getMessage());
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    @Override
    public void export2(TestSqlInfoDTO testSqlInfoDTO, HttpServletResponse response) throws Exception {

        // 参数：表头、字段、条件
        List<String> headList = testSqlInfoDTO.getHeadList();
        List<String> columnList = testSqlInfoDTO.getColumnList();
        List<SqlConditionDTO> conditionList = testSqlInfoDTO.getConditionList();

        List<List<String>> headResultList = Lists.newArrayList();
        List<List<Object>> dataResultList = Lists.newArrayList();
        List<String> columnResultList = Lists.newArrayList();
        String sqlResult = null;

        String module = testSqlInfoDTO.getModule();
        TestSqlInfo testSqlInfo = testSqlInfoMapper.selectSqlColumnByModule(module);
        if (Objects.isNull(testSqlInfo)) {
            throw new Exception("该模块未配置SQL内容");
        }

        String sqlContent = testSqlInfo.getSqlContent();
        if (StringUtils.isBlank(sqlContent)) {
            throw new Exception("SQL内容为空");
        }
        log.info("配置sql内容为：{}", sqlContent);

        // 获取Excel中的表头信息
        for (String excelHead : headList) {
            List<String> list = Lists.newArrayList();
            list.add(excelHead);
            headResultList.add(list);
        }
        log.info("表头：{}", headResultList);

        columnList.forEach(column -> {
            // 转下划线
            String resultStr = CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, column);
            columnResultList.add(resultStr);
        });

        String[] splitSql = sqlContent.split("where");
        String splitColumn = splitSql[0];

        // 替换字段部分(包括字段和表名)
        String columnResult = splitColumn.replace("$", "")
                .replace("{", "")
                .replace("}", "");
        log.info("替换字段结果为：{}", columnResult);

        if (CollectionUtils.isEmpty(conditionList)) {
            // 无条件
            sqlResult = columnResult;
            log.info("无查询条件的sql结果为：{}", sqlResult);
        } else {
            // 至少有一个条件
            String splitCondition = splitSql[1];

            // 替换条件部分(包括条件和其他情况sql、排序、分组、求和等)
            String[] conditions = splitCondition.split(",");
            List<String> conditionTempList = Lists.newArrayList();
            System.out.println("条件分割结果====" + Arrays.toString(conditions));

            //for (SqlConditionDTO conditionParam : conditionList) {
            //    String columnName = conditionParam.getColumnName();
            //    String columnValue = conditionParam.getColumnValue();
            //    // 转下划线
            //    String columnNameResult = CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, columnName);
            //
            //    // 逐条替换
            //    for (String condition : conditions) {
            //        String conditionReplace = PatternUtils.patternCondition(condition, columnNameResult, columnValue);
            //        System.out.println("替换后的值====" + conditionReplace);
            //        // 不包括，说明已经替换，包括说明未传该项的查询条件
            //        if (!conditionReplace.contains("${")) {
            //            conditionTempList.add(conditionReplace);
            //        }
            //    }
            //}

            for (String condition : conditions) {
                for (SqlConditionDTO conditionParam : conditionList) {
                    String columnName = conditionParam.getColumnName();
                    String columnValue = conditionParam.getColumnValue();
                    // 转下划线
                    String columnNameResult = CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, columnName);
                    String conditionReplace = PatternUtils.patternCondition(condition, columnNameResult, columnValue);
                    // 不包括，说明已经替换，包括说明未传该项的查询条件
                    if (!conditionReplace.contains("${")) {
                        conditionTempList.add(conditionReplace);
                        break;
                    }
                }
            }

            log.info("临时list结果为=={}", conditionTempList);

            String joinCondition = String.join(" ", conditionTempList);
            log.info("拼接条件后的结果为：{}", joinCondition);

            String tempSql = columnResult + " where " + joinCondition;
            log.info("临时sql结果为：{}", tempSql);

            // 移除sql中多余的空格、换行符，转换为一条标准的sql
            String normalizeSqlResult = StringUtils.normalizeSpace(tempSql);
            log.info("normalizeSqlResult===={}", normalizeSqlResult);

            // 判断sql组合是否正常，如果两者都没走，说明临时sql已经是完整sql
            boolean flag = Boolean.FALSE;
            if (normalizeSqlResult.contains("where and") || normalizeSqlResult.contains("where or")) {
                List<String> andOrList = Lists.newArrayList("where and", "where or");
                for (String andOr : andOrList) {
                    if (StringUtils.equals(andOr, "where and")) {
                        System.out.println("and匹配===");
                        sqlResult = normalizeSqlResult.replaceFirst("and", "");
                        flag = Boolean.TRUE;
                        break;
                    }

                    if (StringUtils.equals(andOr, "where or")) {
                        System.out.println("or匹配===");
                        sqlResult = normalizeSqlResult.replaceFirst("or", "");
                        flag = Boolean.TRUE;
                        break;
                    }
                }
            }

            if (normalizeSqlResult.contains("where  order by") || normalizeSqlResult.contains("where  group by")) {
                System.out.println("groupAndOrderList匹配===");
                sqlResult = normalizeSqlResult.replace("where", "");
                flag = Boolean.TRUE;
            }

            if (!flag) {
                sqlResult = normalizeSqlResult;
            }
        }

        // 执行sql
        System.out.println("columnResultList字段结果===" + columnResultList);
        log.info("sqlResult的结果===={}", sqlResult);

        if (StringUtils.isNotEmpty(sqlResult)) {

            List<Map<String, Object>> tempList = testSqlInfoMapper.executeSql(sqlResult);
            log.info("SQL查询结果为：{}", tempList);

            if (CollectionUtils.isNotEmpty(tempList)) {
                for (Map<String, Object> map : tempList) {
                    System.out.println("map=======" + map);
                    List<Object> data = Lists.newArrayList();

                    for (String column : columnResultList) {
                        Object columnValue = map.get(column);
                        System.out.println("column===" + column + "，columnValue===" + columnValue);

                        if (Objects.nonNull(columnValue)) {
                            String valueStr = columnValue.toString();
                            System.out.println("valueStr==" + valueStr);
                            data.add(valueStr);
                        }
                    }

                    dataResultList.add(data);
                }
            }

            log.info("导出的数据对象大小为：{}，内容为：{}", dataResultList.size(), dataResultList);
            ExcelWriter excelWriter = null;

            try {
                response.setContentType("application/vnd.openmosix-officiated.spreadsheet.sheet");
                response.setCharacterEncoding("UTF-8");
                String nowTime = DateTimeFormatter.ofPattern("yyyyMMddHHmmss").format(LocalDateTime.now());
                String fileName = URLEncoder.encode(testSqlInfoDTO.getFileName() + nowTime, "UTF-8");
                response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
                // 新建ExcelWriter
                excelWriter = EasyExcel.write(response.getOutputStream()).build();
                WriteSheet sheet = EasyExcel.writerSheet(0, "sheet")
                        .head(headResultList)
                        .includeColumnFiledNames(columnResultList)
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                        .build();
                excelWriter.write(dataResultList, sheet);

                log.info("{}模块，Excel导出成功>>>>>", module);
            } catch (IOException e) {
                log.error("导出异常：{}", e.getMessage());
            } finally {
                if (excelWriter != null) {
                    excelWriter.finish();
                }
            }
        }

    }


}
